package com.zr.d_c.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.d_c.dao.EvaluateDao;
import com.zr.d_c.dao.OrderDao;
import com.zr.d_c.model.Order;
import com.zr.d_c.util.JDBCUtil;

public class OrderDaoImpl implements OrderDao{

	@Override
	public int selectOrdersCount(int sid) {
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sid) scount from d_corder where sid = ? and oissure = 0 ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	@Override
	public List<Order> selectOrderBySid(int sid, int start, int pageSize) {
		List<Order> orders = new ArrayList<Order>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select oid,odishies,osumprices,ocreatetime,oissure,oisdelete,oisevaluate,cname,cphone from d_corder,d_ccustomer WHERE d_corder.cid = d_ccustomer.cid AND d_corder.sid = ? and oissure = 0 ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
					Order order = new Order();
					order.setOid(rs.getInt("oid"));
					order.setOdishies(rs.getString("odishies"));
					order.setOsumprices(rs.getInt("osumprices"));
					order.setOcreatetime(rs.getString("ocreatetime"));
					order.setState("未接受");
					order.setCname(rs.getString("cname"));
					order.setCphone(rs.getString("cphone"));
					orders.add(order);
			}con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orders;
	}

	@Override
	public int sellerAcceptOrderByOid(int oid) {
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update d_corder set oissure = ? where oid = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "1");
			pst.setInt(2, oid);
			int i = pst.executeUpdate();
			if(i>0){
				flag = 1;
			}
			con.close();
			pst.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return flag;
	}

	@Override
	public List<Order> selectHistoryOrderBySid(int sid, int start, int pageSize) {
		List<Order> orders = new ArrayList<Order>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select oid,odishies,osumprices,ocreatetime,oissure,oisdelete,oisevaluate,cname,cphone,otemp1 ");
		sql.append(" from d_corder,d_ccustomer ");
		sql.append(" WHERE d_corder.cid = d_ccustomer.cid AND d_corder.sid = ? ");
		sql.append(" and oissure = 1 ");
		sql.append(" order by oid desc ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
					Order order = new Order();
					EvaluateDao evaluate = new EvaluateDaoImpl();
					order.setOid(rs.getInt("oid"));
					order.setOdishies(rs.getString("odishies"));
					order.setOsumprices(rs.getInt("osumprices"));
					order.setOcreatetime(rs.getString("ocreatetime"));
					if(rs.getInt("oissure")==1){
						if(rs.getString("otemp1").equals("1")){
							order.setState("已撤销");
						}else{
							order.setState("已接受");
						}
						
					}else{
						order.setState("未接受");
					}
					if(rs.getInt("oisevaluate")==0){
					order.setEvaluate("");
					}else{
//						获取对应订单的评论内容
						order.setEvaluate(evaluate.selectEvaluateByOid(rs.getInt("oid")).getEtext());
					}
					order.setCname(rs.getString("cname"));
					order.setCphone(rs.getString("cphone"));
					orders.add(order);
			}
			con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orders;
	}
	@Override
	public int selectHistoryOrdersCount(int sid) {
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sid) scount from d_corder where sid = ? and oissure = 1 ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
			con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}
	
	@Override
	public List<Order> selectCurrentOrderByCid(int cid, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Order> orders = new ArrayList<Order>();
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select * from d_corder,d_ccustomer WHERE d_corder.cid = d_ccustomer.cid AND d_corder.cid = ? and oissure = 0");
		sql.append(" limit ?,?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Order order = new Order();
				order.setOid(rs.getInt("oid"));
				order.setOdishies(rs.getString("odishies"));
				order.setOsumprices(rs.getInt("osumprices"));
				order.setOcreatetime(rs.getString("ocreatetime"));
				order.setOissure(rs.getInt("oissure"));
				order.setCid(rs.getInt("cid"));
				order.setSid(rs.getInt("sid"));
				orders.add(order);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return orders;
	}

	@Override
	public int selectCountCurrentOrderByCid(int cid) {
		// TODO Auto-generated method stub
		int count = 0;
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select count(*) countNum from d_corder where cid = ? and oissure = 0");
		
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("countNum");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return count;
	}

	@Override
	public int insertOrder(Order o) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("insert into d_corder (odishies,osumprices,ocreatetime,oissure,oisdelete,oisevaluate,sid,cid,otemp1) values(?,?,?,?,?,?,?,?,'0')");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, o.getOdishies());
			pst.setInt(2, o.getOsumprices());
			pst.setString(3, o.getOcreatetime());
			pst.setInt(4, o.getOissure());
			pst.setInt(5, o.getOisdelete());
			pst.setInt(6, o.getOisevaluate());
			pst.setInt(7, o.getSid());
			pst.setInt(8, o.getCid());
			int i = pst.executeUpdate();
			if(i > 0){
				 flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		
		return flag;
	}

	@Override
	public List<Order> selectHistoryOrderBySid(int sid, int start, int pageSize, String searchname) {
		List<Order> orders = new ArrayList<Order>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select oid,odishies,osumprices,ocreatetime,oissure,oisdelete,oisevaluate,cname,cphone,otemp1 ");
		sql.append(" from d_corder,d_ccustomer ");
		sql.append(" WHERE d_corder.cid = d_ccustomer.cid AND d_corder.sid = ? and cname like ? ");
		sql.append(" and oissure = 1 ");
		sql.append(" order by oid desc ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2,  "%"+searchname+"%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
					Order order = new Order();
					EvaluateDao evaluate = new EvaluateDaoImpl();
					order.setOid(rs.getInt("oid"));
					order.setOdishies(rs.getString("odishies"));
					order.setOsumprices(rs.getInt("osumprices"));
					order.setOcreatetime(rs.getString("ocreatetime"));

					if(rs.getInt("oissure")==1){
						if(rs.getString("otemp1").equals("1")){
							order.setState("已撤销");
						}else{
							order.setState("已接受");
						}
					}else{
						order.setState("未接受");
					}
					if(rs.getInt("oisevaluate")==0){
					order.setEvaluate("");
					}else{
						order.setEvaluate(evaluate.selectEvaluateByOid(rs.getInt("oid")).getEtext());
					}
					order.setCname(rs.getString("cname"));
					order.setCphone(rs.getString("cphone"));
					orders.add(order);
			

			}
			con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orders;
	}

	@Override
	public List<Order> selectOrderBySid(int sid, int start, int pageSize, String searchname) {
		List<Order> orders = new ArrayList<Order>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select oid,odishies,osumprices,ocreatetime,oissure,oisdelete,oisevaluate,cname,cphone from d_corder,d_ccustomer WHERE d_corder.cid = d_ccustomer.cid AND d_corder.sid = ?  and oissure = 0 and oissure = 0 and cname like ?");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, "%"+searchname+"%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				if(rs.getInt("oissure")==1){
					continue;
				}else{
					Order order = new Order();
					order.setOid(rs.getInt("oid"));
					order.setOdishies(rs.getString("odishies"));
					order.setOsumprices(rs.getInt("osumprices"));
					order.setOcreatetime(rs.getString("ocreatetime"));
					order.setState("未接受");
					order.setCname(rs.getString("cname"));
					order.setCphone(rs.getString("cphone"));
					orders.add(order);
				}

			}
			con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return orders;
	}
	@Override
	public List<Order> selectHistoryOrderByCid(int cid, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Order> orders = new ArrayList<Order>();
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select * from d_corder where cid = ? and oissure = 1 and oisdelete = 1");
		sql.append(" order by oid desc ");
		sql.append(" limit ?,?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Order order = new Order();
				order.setOid(rs.getInt("oid"));
				order.setOdishies(rs.getString("odishies"));
				order.setOsumprices(rs.getInt("osumprices"));
				order.setOcreatetime(rs.getString("ocreatetime"));
				order.setOissure(rs.getInt("oissure"));
				order.setCid(rs.getInt("cid"));
				order.setSid(rs.getInt("sid"));
				order.setOisevaluate(rs.getInt("oisevaluate"));
				order.setOtemp1(rs.getString("otemp1"));
				orders.add(order);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return orders;
	}

	@Override
	public int selectCountHistoryOrderByCid(int cid) {
		// TODO Auto-generated method stub
		int count = 0;
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select count(*) countNum from d_corder where cid = ? and oissure = 1 and oisdelete = 1");
		
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("countNum");
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return count;
	}

	@Override
	public int updateOisdeleteByOid(int oid) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("update d_corder set oisdelete = 0 where oid = ?");
		
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oid);
			int i = pst.executeUpdate();
			if(i > 0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return flag;
	}

	@Override
	public int updateOisevaluateByOid(int oid) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("update d_corder set oisevaluate = 1 where oid = ?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oid);
			int i = pst.executeUpdate();
			if(i > 0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return flag;
	}

	@Override
	public int updateOtemp1(int oid) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = null;
		PreparedStatement pst = null;
		
		StringBuffer sql = new StringBuffer("update d_corder set oissure = 1,otemp1='1' where oid = ?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, oid);
			int i = pst.executeUpdate();
			if(i > 0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return flag;
	}

	@Override
	public int selectCountHistoryOrderByCid(int cid, String searchname) {
		// TODO Auto-generated method stub
		int count = 0;
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select count(*) countNum from d_corder,d_cseller where cid = ? and oissure = 1 and oisdelete = 1 and d_corder.sid=d_cseller.sid and sname like ?");
		
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			pst.setString(2, "%"+searchname+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("countNum");
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return count;
	}

	@Override
	public List<Order> selectHistoryOrderByCid(int cid, int start, int pageSize, String searchname) {
		// TODO Auto-generated method stub
		List<Order> orders = new ArrayList<Order>();
		Connection con = null;
		PreparedStatement pst = null;
		StringBuffer sql = new StringBuffer("select * from d_corder,d_cseller where cid = ? and oissure = 1 and oisdelete = 1 and d_corder.sid=d_cseller.sid and sname like ?");
		sql.append(" order by oid desc ");
		sql.append(" limit ?,?");
		try {
			con = JDBCUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			pst.setString(2, "%"+searchname+"%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Order order = new Order();
				order.setOid(rs.getInt("oid"));
				order.setOdishies(rs.getString("odishies"));
				order.setOsumprices(rs.getInt("osumprices"));
				order.setOcreatetime(rs.getString("ocreatetime"));
				order.setOissure(rs.getInt("oissure"));
				order.setCid(rs.getInt("cid"));
				order.setSid(rs.getInt("sid"));
				order.setOisevaluate(rs.getInt("oisevaluate"));
				order.setOtemp1(rs.getString("otemp1"));
				orders.add(order);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closeJDBC(pst, con);
		}
		return orders;
	}
}
